﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using SBS;

namespace Data
{
   public class DB
    {
        
        public static string ConnectionString
        {
            get { return @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SBSdb.mdf;Integrated Security=True;User Instance=True"; }
        }

       

        
        public static bool TilfojScooter(int Knr, Scooter S)
        {
            string sql = "INSERT INTO Scooter VALUES('" + S.RegNr + "','" + null + "','" + S.Aar + "','" + S.AntKm + "','" + S.Model + "','" + S.Certifikat + "','" + Knr + "')";
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                
                
                conn.Open();
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                
                return true;
            }


            catch
            {

                return false;


            }

           finally
           {
                conn.Close();
           }
        }

        public static List<KeyValuePair<int,string>> HentCertifikatListe()
        {
            List<KeyValuePair<int, string>> CertListe = new List<KeyValuePair<int, string>>();
            string sqlScoCert = "SELECT * FROM Certificat";
            
            SqlConnection conn = new SqlConnection(ConnectionString);

            try
            {
                conn.Open();
                SqlCommand com = new SqlCommand(sqlScoCert, conn);
                SqlDataReader ScoCertReader = com.ExecuteReader();

                
                while(ScoCertReader.Read())
                {
                CertListe.Add(new KeyValuePair<int,string>(int.Parse(ScoCertReader[0].ToString()),ScoCertReader[1].ToString()));
                }
            
                return CertListe;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        
       
        public static Dictionary<int,SBS.Vare> HentVareListe()

        {
            Dictionary<int, SBS.Vare> Liste = new Dictionary<int, SBS.Vare>();

            string sql = "select * from Varetype";

            SqlConnection conn = new SqlConnection(ConnectionString);

            try
            {
                conn.Open();

                SqlCommand com = new SqlCommand(sql, conn);

                SqlDataReader reader = com.ExecuteReader();

                while (reader.Read())
                {
                    // Hvis kollonne 4 er DBNull er det en reservedel ellers ydelse
                    if (reader.IsDBNull(3))
                    {
                        SBS.Reservedel r = new Reservedel((int)reader[0], reader[1].ToString(), (double)reader[2], reader[4].ToString());
                        Liste.Add(r.VareNr,r);

                    }
                    else
                    {
                        SBS.Ydelse y = new Ydelse((int)reader[0], reader[1].ToString(), (double)reader[2], (int)reader[3]);
                        Liste.Add(y.VareNr,y);
                    }
                    
                }
                return Liste;




            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                conn.Close();
            }

        }

    }
}
